package org.a1.ectltool.control;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.a1.ectltool.boundary.MainFrame;
import org.a1.ectltool.entity.Sales;

public class DWHManager
{
	public static final DWHManager INSTANCE = new DWHManager();
	
	private static final String HOST = "127.0.0.1";
	private static final String SID = "proftaak";
	private static final String USERNAME = "dmuser";
	private static final String PASSWORD = "Admin123";
	private static final int PORT = 1521;
	
	private Connection conn;
	private PreparedStatement statement;
	private ResultSet result;
	
	public static DWHManager getManager()
	{
		return INSTANCE;
	}
	
	private DWHManager()
	{
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@" + HOST + ":" + PORT + ":" + SID, USERNAME, PASSWORD);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public void addSales(ArrayList<Sales> sales)
	{
		//TODO: add sales to datawarehouse
		MainFrame.getFrame().addLog("Storing data in warehouse");
		
		for(Sales sale : sales)
		{
			long timeID = -1;
			long productID = -1;
			long superMarketID = -1;
			try
			{
				statement = conn.prepareStatement(	"SELECT			TIMEID" +
													" FROM			TIME " +
													" WHERE			YEAR=" + sale.getTime().getYear() +
													" AND			QUARTER=" + sale.getTime().getQuater());
				result = statement.executeQuery();
				
				if(result.next())
				{
					timeID = result.getLong(1);
				}
				else
				{
					statement.close();
					result.close();
					
					statement = conn.prepareStatement("INSERT INTO TIME(YEAR, QUARTER) VALUES(" + sale.getTime().getYear() + ", " + sale.getTime().getQuater() + ")", new String[] { "TIMEID" });
					
					if(statement.executeUpdate() > 0)
					{
						result = statement.getGeneratedKeys();
						while(result.next())
						{
							timeID = result.getLong(1);
						}
					}
				}
				
				result.close();
				statement.close();
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
			finally
			{
				try
				{
					result.close();
					statement.close();
				}
				catch(SQLException e)
				{
					e.printStackTrace();
				}
			}
			
			try
			{
				
				statement = conn.prepareStatement(	"SELECT		SUPERMARKETID" +
													" FROM		SUPERMARKET" +
													" WHERE		NAME='" + sale.getSuperMarket().getName() + "'");
				result = statement.executeQuery();
				if(result.next())
				{
					superMarketID = result.getLong(1);
				}
				else
				{
					statement.close();
					result.close();
					
					statement = conn.prepareStatement("INSERT INTO SUPERMARKET(NAME) VALUES('" + sale.getSuperMarket().getName() + "')", new String[] {"SUPERMARKETID" } );
					
					if(statement.executeUpdate() > 0)
					{
						result = statement.getGeneratedKeys();
						while(result.next())
						{
							superMarketID = result.getLong(1);
						}
					}
				}
				
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
			finally
			{
				try
				{
					result.close();
					statement.close();
				}
				catch(SQLException e)
				{
					e.printStackTrace();
				}
			}
			
			try
			{	
				statement = conn.prepareStatement(	"SELECT		PRODUCTID" +
													" FROM		PRODUCT"+
													" WHERE		BRAND='" + sale.getProduct().getBrand() + "'" +
													" AND		MACHINE='" + sale.getProduct().getMachine() + "'" +
													" AND		EXPERIENCE='" + sale.getProduct().getExperience() + "'" +
													" AND		PTYPE='" + sale.getProduct().getType() + "'" +
													" AND		KIND='" + sale.getProduct().getKind() + "'" +
													" AND		PSIZE='" + sale.getProduct().getSize() + "'" +
													" AND		FLAVOUR='" + sale.getProduct().getFlavour() + "'" +
													" AND		UPC="+ sale.getProduct().getUPC());
				
				result = statement.executeQuery();
				if(result.next())
				{
					productID = result.getLong(1);
				}
				else
				{
					statement.close();
					result.close();
					
					statement = conn.prepareStatement("INSERT INTO PRODUCT(BRAND, MACHINE, EXPERIENCE, PTYPE, KIND, PSIZE, FLAVOUR, UPC) VALUES(" +
														"'" + sale.getProduct().getBrand() + "'," +
														"'" + sale.getProduct().getMachine() + "'," +
														"'" + sale.getProduct().getExperience() + "'," +
														"'" + sale.getProduct().getType() + "'," +
														"'" + sale.getProduct().getKind() + "'," +
														"'" + sale.getProduct().getSize() + "'," +
														"'" + sale.getProduct().getFlavour() + "'," +
														sale.getProduct().getUPC() +
													  ")", new String[] { "PRODUCTID" });
					
					if(statement.executeUpdate() > 0)
					{
						result = statement.getGeneratedKeys();
						while(result.next())
						{
							productID = result.getLong(1);
						}
					}
				}
			}
			catch(SQLException e)
			{
				e.printStackTrace();
				return;
			}
			finally
			{
				try
				{
					result.close();
					statement.close();
				}
				catch(SQLException e)
				{
					e.printStackTrace();
				}
			}
			
			try
			{
				statement = conn.prepareStatement(	"SELECT	EUROSALES" +
													" FROM	SALES" +
													" WHERE	EUROSALES=" + sale.getEuroSales() +
													" AND	SUPERMARKETID =" + superMarketID +
													" AND	TIMEID=" + timeID +
													" AND	PRODUCTID=" + productID);
				result = statement.executeQuery();
				
				if(result.next())
				{
					System.out.println("Already in datawarehouse!");
				}
				else
				{
					statement.close();
					result.close();
					
					statement = conn.prepareStatement(	"INSERT INTO	SALES(EUROSALES, SUPERMARKETID, TIMEID, PRODUCTID) VALUES(" +
														sale.getEuroSales() + "," +
														superMarketID + "," +
														timeID + "," +
														productID +
														")");
					if(statement.executeUpdate() > 0)
					{
						System.out.println("Time: " + timeID + ", Supermarket: " + superMarketID + ", Product: " + productID + ", Sales: " + sale.getEuroSales());
					}
				}
			}
			catch(SQLException e)
			{
				e.printStackTrace();
				return;
			}
			finally
			{
				try
				{
					result.close();
					statement.close();
				}
				catch(SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
	}
	
	public void stop()
	{
		System.out.println("Closing connection!");
		if(conn != null)
		{
			try
			{
				conn.close();
				System.out.println("Connection closed!");
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
}
